package com.lnsystem.dao.impl;

import java.util.ArrayList;
import java.util.List;
import java.io.Serializable;
import java.math.BigInteger;
import java.util.List;

import javax.persistence.criteria.From;

import org.springframework.transaction.annotation.Transactional;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projection;
import org.hibernate.criterion.ProjectionList;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Restrictions;
import org.joda.time.DateTime;
import org.joda.time.LocalDate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.stereotype.Repository;

import com.lnsystem.constants.LNSystemConstants;
import com.lnsystem.dao.StockDao;
import com.lnsystem.model.MovingAverage;
import com.lnsystem.model.Stock;
import com.lnsystem.model.StockDailyData;
import com.lnsystem.model.StockRating;
import com.lnsystem.model.StockWeeklyData;
import com.lnsystem.service.ScheduledTask;
import com.sun.istack.internal.logging.Logger;

@Repository
public class StockDaoHibernateImpl implements StockDao {

	Logger logger = Logger.getLogger(StockDaoHibernateImpl.class);

	@Autowired
	SessionFactory sessionFactory;

	@Override
	@Transactional
	public Stock getStockById(int id) {
		Session session = sessionFactory.getCurrentSession();
		Stock stock = (Stock) session.get(Stock.class, id);
		return stock;
	}

	@Override
	@Transactional
	public Stock getStockBySymbol(String symbol) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		List<Stock> stockList = session.createCriteria(Stock.class)
				.add(Restrictions.eq("symbol", symbol)).list();
		if (stockList != null && stockList.size() > 0) {
			return stockList.get(0);
		}
		// session.close();
		return null;
	}

	@Override
	public List<Stock> getAllStocks() {
		Session session = sessionFactory.getCurrentSession();
		@SuppressWarnings("unchecked")
		List<Stock> stockList = session.createQuery("from Stock").list();

		// session.close();
		return stockList;
	}

	@Override
	@Transactional
	public int addStock(Stock stock) {
		Session session = sessionFactory.getCurrentSession();

		// List stockList =
		// session.createCriteria(Stock.class).add(Restrictions.eq("symbol",
		// stock.getSymbol())).list();

		// if(stockList.size()<1) {
		// Transaction tx = session.beginTransaction();
		session.saveOrUpdate(stock);
		// tx.commit();
		Serializable id = session.getIdentifier(stock);

		// session.close();

		//session.flush();
		flushAndClear();

		return (Integer) id;
		// } else {
		// return -1;
		// }

	}

	@Override
	@Transactional
	public int updateStockRatingData(StockRating stockRating) {
		Session session = sessionFactory.getCurrentSession();

		// List stockList =
		// session.createCriteria(Stock.class).add(Restrictions.eq("symbol",
		// stock.getSymbol())).list();

		// if(stockList.size()<1) {
		// Transaction tx = session.beginTransaction();
		session.saveOrUpdate(stockRating);
		// tx.commit();
		Serializable id = session.getIdentifier(stockRating);

		// session.close();

		session.flush();

		return (Integer) id;
		// } else {
		// return -1;
		// }

	}
	@Override
	@Transactional
	public int updateStock(Stock stock) {
		Session session = sessionFactory.getCurrentSession();
		// Transaction tx = session.beginTransaction();
		session.saveOrUpdate(stock);
		// tx.commit();
		Serializable id = session.getIdentifier(stock);
		// session.close();
		return (Integer) id;
	}

	public double getMA(StockDailyData sd, int period, int type) {
		double maValue = 0;
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		List<MovingAverage> maList = session
				.createCriteria(MovingAverage.class)
				.add(Restrictions.eq("stockDailyData", sd))
				.add(Restrictions.eq("period", period))
				.add(Restrictions.eq("type", type)).list();
		if (maList != null && maList.size() > 0) {
			maValue = maList.get(0).getMaValue();
		}
		// session.close();
		return maValue;
	}

	@Override
	public double getSMA(StockDailyData sd, int period) {
		return getMA(sd, period, 0);
	}

	@Override
	public double getEMA(StockDailyData sd, int period) {
		return getMA(sd, period, 1);
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockDailyData> getStockDailyData(Stock s,boolean sortDesc) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		
		Criteria criteria = session.createCriteria(StockDailyData.class);

		criteria.add(Restrictions.eq("stockId", s));
		if(sortDesc) {
			criteria.addOrder(Order.desc("date"));
		}
		
		criteria.setMaxResults(LNSystemConstants.MAX_DAYS_FOR_DAILY_TIMEFRAME);
		List<StockDailyData> dailyDataList = criteria.list();
		// session.close();
		return dailyDataList;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public StockDailyData getStockDailyData(String symbol, LocalDate date) {
		
		Session session = sessionFactory.getCurrentSession();
		
/*		Criteria criteria = session.createCriteria(StockDailyData.class);

		criteria.add(Restrictions.eq("symbol", symbol));
		criteria.add(Restrictions.eq("date", date.toDate()));

		*/
		Query query = session
				.createQuery("from StockDailyData where symbol = :symbol and date = :date");
		query.setParameter("symbol", symbol);
		query.setParameter("date", date.toDate());
		
		query.setMaxResults(1);
		StockDailyData dailyData = (StockDailyData) query.uniqueResult();
		
		//StockDailyData dailyData = (StockDailyData) criteria.uniqueResult();
		// session.close();
		return dailyData;
	}

	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public double getMostRecentClose(Stock s) {
		
		StockDailyData recent = getMostRecentDailyData(s);
		if(recent != null) {
			return recent.getClose();
		} else {
			return -1;
		}
		
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public StockDailyData getMostRecentDailyData(Stock s) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session
				.createQuery("from StockDailyData where stockId = :stockId order by dateTime DESC");
		query.setEntity("stockId", s);
		query.setMaxResults(1);
		StockDailyData last = (StockDailyData) query.uniqueResult();
		// session.close();
		return last;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockDailyData> getStockDailyData(Stock s, LocalDate startDate,
			LocalDate endDate, boolean sortDesc) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		
		Criteria criteria = session.createCriteria(StockDailyData.class);
		criteria.add(Restrictions.eq("stockId", s));
		criteria.add(Restrictions.ge("date", startDate.toDate()));
		criteria.add(Restrictions.le("date", endDate.toDate()));
		if(sortDesc) {
			criteria.addOrder(Order.desc("date"));
		}
		criteria.setMaxResults(LNSystemConstants.MAX_DAYS_FOR_DAILY_TIMEFRAME);
		
		List<StockDailyData> dailyDataList = criteria.list();
		
		// session.close();
		return dailyDataList;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockDailyData> getStockDailyDataBeforeDate(Stock s,
			LocalDate date, boolean sortDesc) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Criteria criteria = session.createCriteria(StockDailyData.class);
		

		criteria.add(Restrictions.eq("stockId", s));
		criteria.add(Restrictions.le("date", date.toDate()));
		if(sortDesc) {
			criteria.addOrder(Order.desc("date"));
		}
		criteria.setMaxResults(LNSystemConstants.MAX_DAYS_FOR_DAILY_TIMEFRAME);
		
		List<StockDailyData> dailyDataList = criteria.list();
		// session.close();
		return dailyDataList;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockDailyData> getStockDailyDataAfterDate(Stock s,
			LocalDate date, boolean sortDesc) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		Criteria criteria = session.createCriteria(StockDailyData.class);

		criteria.add(Restrictions.eq("stockId", s));
		criteria.add(Restrictions.ge("date", date.toDate()));
		if(sortDesc) {
			criteria.addOrder(Order.desc("date"));
		}
		criteria.setMaxResults(LNSystemConstants.MAX_DAYS_FOR_DAILY_TIMEFRAME);
		
		
		List<StockDailyData> dailyDataList = criteria.list();		
		// session.close();
		return dailyDataList;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public BigInteger getAverageVolume(Stock s) {
		Session session = sessionFactory.getCurrentSession();

		String hql = "SELECT avg(volume) FROM StockDailyData where stockId="
				+ s.getId();
		Query query = session.createQuery(hql);
		BigInteger averageVolume = (BigInteger) query.uniqueResult();

		return averageVolume;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public BigInteger getMaxDownDayVolume(StockDailyData dailyData) {
		
		Session session = sessionFactory.getCurrentSession();
		
		String hql = "select max(volume) from (select * from stockdaily  where symbol ='"+dailyData.getSymbol()+"' "
				+ " and datetime <= '"+dailyData.getDate()+"' order by datetime  desc limit 10) a"
				+ " where a.price_action='R' OR a.price_action='H' ";

		Query query = session.createSQLQuery(hql);
		BigInteger volume = (BigInteger) query.uniqueResult();
		if(volume != null) {
			return volume;
		} else {
			logger.info("This query returned the volume as null; Query-->: select max(volume) from (select * from stockdaily  where symbol ='"+dailyData.getSymbol()+"' "
				+ " and datetime <= '"+dailyData.getDate()+"' order by datetime  desc limit 10) a"
				+ " where a.price_action='R'");
		}
		return BigInteger.ZERO;
	}
	
	
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	@Transactional
	public BigInteger getAverageVolume(Stock s, int days) {
		Session session = sessionFactory.getCurrentSession();

/*
 * learnt that it is not quite possible to do limit/maxResults() on an inner query using hibernate/HSQL.
 * 
		Query query = session
				.createQuery("from StockWeeklyData where stockId = :stockId order by id DESC");
		query.setEntity("stockId", s);
	

		ProjectionList projList = Projections.projectionList();
		projList.add(Projections.avg("volume"));
		
		Criteria criteria = session.createCriteria(StockDailyData.class);
		criteria.setMaxResults(days);
		criteria.addOrder(Order.desc("id"));
		criteria.setProjection(projList);
		
*/
		String hql = "select avg(volume) from stockdaily where id in( "
				+ " select id from  stockdaily where symbol='"+s.getSymbol()+"'  order by id desc limit "+LNSystemConstants.DAYS_FOR_AVGVOL_CALCULATION+")";

		Query query = session.createSQLQuery(hql);
		BigInteger averageVolume = (BigInteger) query.uniqueResult();

		
		return averageVolume;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<Stock> getStocksOverCertainVolume(double volume) {
		Session session = sessionFactory.getCurrentSession();

		String hql = "FROM Stock s, StockDailyData sd"
				+ " where sd.stockId=s.id ";
		Query query = session.createQuery(hql);
		double averageVolume = (Double) query.uniqueResult();

		Criteria criteria = session.createCriteria(Stock.class, "s");
		criteria.createAlias("s.stockDailyData", "sd");
		criteria.add(Restrictions.ge("sd.volume", volume));
		criteria.addOrder(Order.asc("symbol"));

		@SuppressWarnings("unchecked")
		List<Stock> stockList = criteria.list();

		// session.close();
		return stockList;
	}

	@Override
	public List<Stock> getStocksOverCertainPriceVolume(double price,
			double volume) {
		Session session = sessionFactory.getCurrentSession();

		SQLQuery query = session
				.createSQLQuery("select s.*  from stock s "
						+ " inner join (select stockid, avg(volume) averagevolume from StockDaily  group by  stockid) sd1 on s.id = sd1.stockid "
						+ " inner join (select stockid, max(datetime) datetime, max(id) sdid from StockDaily  group by stockid) sd2 on s.id = sd2.stockid "
						+ " inner join (select id, close from  StockDaily group by id) sd3 on sd3.id = sd2.sdid "
						+ " where sd1.averagevolume > :volume "
						+ " and sd3.close > :price order by s.symbol");

		query.setDouble("volume", volume);
		query.setDouble("price", price);

		query.addEntity(Stock.class);
		List<Stock> stockList = query.list();

		// session.close();
		return stockList;
	}

	@Override
	public List<Stock> getStocksByPriceRangeOverCertainVolume(double minPrice,
			double maxPrice, double volume) {
		Session session = sessionFactory.getCurrentSession();

		SQLQuery query = session
				.createSQLQuery("select s.*  from stock s "
						+ " inner join (select stockid, avg(volume) averagevolume from StockDaily  group by  stockid) sd1 on s.id = sd1.stockid "
						+ " inner join (select stockid, max(datetime) datetime, max(id) sdid from StockDaily  group by stockid) sd2 on s.id = sd2.stockid "
						+ " inner join (select id, close from  StockDaily group by id) sd3 on sd3.id = sd2.sdid "
						+ " where sd1.averagevolume > :volume "
						+ " and sd3.close between :min_price and :max_price order by s.symbol");

		query.setDouble("volume", volume);
		query.setDouble("min_price", minPrice);
		query.setDouble("max_price", maxPrice);

		query.addEntity(Stock.class);
		List<Stock> stockList = query.list();

		// session.close();
		return stockList;
	}

	/*
	 * @Override public int deleteRow(int id) { Session session =
	 * sessionFactory.openSession(); Transaction tx =
	 * session.beginTransaction(); Employee employee = (Employee)
	 * session.load(Employee.class, id); session.delete(employee); tx.commit();
	 * Serializable ids = session.getIdentifier(employee); session.close();
	 * return (Integer) ids; }
	 */
	
	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockWeeklyData> getStockWeeklyData(Stock s) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		List<StockWeeklyData> dailyDataList = session
				.createCriteria(StockWeeklyData.class)
				.add(Restrictions.eq("stockId", s)).list();
		// session.close();
		return dailyDataList;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public StockWeeklyData getMostRecentWeeklyData(Stock s) {
		Session session = sessionFactory.getCurrentSession();
		Query query = session
				.createQuery("from StockWeeklyData where stockId = :stockId order by dateTime DESC");
		query.setEntity("stockId", s);
		query.setMaxResults(1);
		StockWeeklyData last = (StockWeeklyData) query.uniqueResult();
		// session.close();
		return last;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockWeeklyData> getStockWeeklyData(Stock s, LocalDate startDate,
			LocalDate endDate) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		List<StockWeeklyData> dailyDataList = session
				.createCriteria(StockWeeklyData.class)
				.add(Restrictions.eq("stockId", s))
				.add(Restrictions.ge("date", startDate.toDate()))
				.add(Restrictions.le("date", endDate.toDate()))
				.addOrder(Order.desc("date")).list();

		// session.close();
		return dailyDataList;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockWeeklyData> getStockWeeklyDataBeforeDate(Stock s,
			LocalDate date) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		List<StockWeeklyData> dailyDataList = session
				.createCriteria(StockWeeklyData.class)
				.add(Restrictions.eq("stockId", s))
				.add(Restrictions.le("date", date.toDate()))
				.addOrder(Order.desc("date")).list();

		// session.close();
		return dailyDataList;
	}

	/**
	 * {@inheritDoc}
	 */
	@Override
	public List<StockWeeklyData> getStockWeeklyDataAfterDate(Stock s,
			LocalDate date) {
		Session session = sessionFactory.getCurrentSession();

		@SuppressWarnings("unchecked")
		List<StockWeeklyData> dailyDataList = session
				.createCriteria(StockWeeklyData.class)
				.add(Restrictions.eq("stockId", s))
				.add(Restrictions.ge("date", date.toDate()))
				.addOrder(Order.desc("date")).list();
		// session.close();
		return dailyDataList;
	}	
	
	/**
	 * {@inheritDoc}
	 */
	@Override	
	public double getHighForPivotCalculation(Stock s, DateTime dateTime, int timeFrame) {
		
		Double highOfPreviousMonth = (double) 0;
		Session session = sessionFactory.getCurrentSession();

		if(dateTime == null) {
			dateTime = DateTime.now();
		}
		if(timeFrame == LNSystemConstants.DAILY_TIMEFRAME) {
			LocalDate dateLastMonth = dateTime.minusMonths(1).toLocalDate();
			
			LocalDate fromDate = dateLastMonth.dayOfMonth().withMinimumValue();
			LocalDate toDate = dateLastMonth.dayOfMonth().withMaximumValue();
			
			ProjectionList projList = Projections.projectionList();
			projList.add(Projections.max("high"));
			
			highOfPreviousMonth = (Double) session
			.createCriteria(StockDailyData.class)
			.add(Restrictions.ge("date", fromDate.toDate()))
			.add(Restrictions.le("date", toDate.toDate()))
			.add(Restrictions.eq("stockId", s))
			.setProjection(projList)
			.uniqueResult();
			
		}
		return highOfPreviousMonth;
	}
	
	/**
	 * {@inheritDoc}
	 */
	@Override	
	public double getLowForPivotCalculation(Stock s, DateTime dateTime, int timeFrame) {
		
		Double lowOfPreviousMonth = (double) 0;
		Session session = sessionFactory.getCurrentSession();

		if(dateTime == null) {
			dateTime = DateTime.now();
		}
		
		if(timeFrame == LNSystemConstants.DAILY_TIMEFRAME) {
			LocalDate dateLastMonth = dateTime.minusMonths(1).toLocalDate();
			
			LocalDate fromDate = dateLastMonth.dayOfMonth().withMinimumValue();
			LocalDate toDate = dateLastMonth.dayOfMonth().withMaximumValue();
			
			ProjectionList projList = Projections.projectionList();
			projList.add(Projections.min("low"));
			
			lowOfPreviousMonth = (Double) session
			.createCriteria(StockDailyData.class)
			.add(Restrictions.ge("date", fromDate.toDate()))
			.add(Restrictions.le("date", toDate.toDate()))
			.add(Restrictions.eq("stockId", s))
			.setProjection(projList)
			.uniqueResult();
			
		}
		return lowOfPreviousMonth;
	}
	
	
	/**
	 * {@inheritDoc}
	 */
	@Override	
	public double getCloseForPivotCalculation(Stock s, DateTime dateTime, int timeFrame) {
		Double closeOfPreviousMonth = null;
		int iteration = 0;
		Session session = sessionFactory.getCurrentSession();

		if(dateTime == null) {
			dateTime = DateTime.now();
		}
		
		if(timeFrame == LNSystemConstants.DAILY_TIMEFRAME) {
			LocalDate dateLastMonth = dateTime.minusMonths(1).toLocalDate();
			
			//LocalDate fromDate = dateLastMonth.dayOfMonth().withMinimumValue();
			LocalDate toDate = dateLastMonth.dayOfMonth().withMaximumValue();
			
			ProjectionList projList = Projections.projectionList();
			projList.add(Projections.property("close"));
			
			while(closeOfPreviousMonth == null && iteration<5) {
				closeOfPreviousMonth = (Double) session
						.createCriteria(StockDailyData.class)
						.add(Restrictions.eq("date", toDate.toDate()))
						.add(Restrictions.eq("stockId", s))
						.setProjection(projList)
						.uniqueResult();
				
				iteration++;
				toDate = toDate.minusDays(1); //keep checking until there is data 
			}

			
		}
		return closeOfPreviousMonth;
	}
	
	
	@Override
	@Transactional
	public void flushAndClear() {
		Session session = sessionFactory.getCurrentSession();
        session.flush();
        session.clear();
	}
}